<!DOCTYPE html>
<html>
  <!-- meta/link... -->
  



<head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8">
  <meta content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=0" name="viewport">
  <!-- Global site tag (gtag.js) - Google Analytics -->


  <title>SQL语句大全，所有的SQL都在这里 | BigBoss</title>

  <link rel="icon" type="image/x-icon, image/vnd.microsoft.icon" href="/favicon.ico">
  <link rel="stylesheet" href="https://at.alicdn.com/t/font_1911880_c1nvbyezg17.css">
  <link href="https://cdn.jsdelivr.net/gh/inkss/fontawesome@5.15.3/css/all.min.css" rel="stylesheet">
  <link href="/js/swiper/swiper@5.4.1.min.css" rel="stylesheet">
  
  
    <script>
        var themeModelId = '3';
        if (themeModelId) {
            localStorage.setItem('modelId', themeModelId);
        }
    </script>
    
    <script src="https://cdn.jsdelivr.net/gh/yuang01/live2d-widget@latest/autoload.js"></script>
    <script>
        var live2dOpen = eval('true') || false;
        if (!live2dOpen) {
            localStorage.setItem('waifu-display', 1609323474481);
        }
    </script>
  
  
  
<link rel="stylesheet" href="/css/animate.min.css">

  
<link rel="stylesheet" href="/css/style.css">

  
  
    
<link rel="stylesheet" href="/js/fancybox/jquery.fancybox.min.css">

  
  
    
<link rel="stylesheet" href="/js/shareJs/share.min.css">

  
  <style>
        @media (max-width: 992px) {
            #waifu {
                display: none;
            }
        }
    </style>
    <script src="//cdn.bootcss.com/pace/1.0.2/pace.min.js"></script>
    <link href="//cdn.bootcss.com/pace/1.0.2/themes/pink/pace-theme-flash.css" rel="stylesheet">

    
        <script src="/js/valine/av-min@3.0.4.js"></script>
        <script src="https://cdn.jsdelivr.net/gh/HCLonely/Valine@latest/dist/Valine.min.js"></script>
    

    <!-- import link -->
    
        
            
        
            
        
    
    <!-- import script -->
    
        
            
        
            
        
    

<meta name="generator" content="Hexo 6.1.0"><link rel="alternate" href="/atom.xml" title="BigBoss" type="application/atom+xml">
</head>

  
  <!-- 依赖于jquery和vue -->
  
    
<script src="https://cdn.jsdelivr.net/npm/jquery@3.5.1/dist/jquery.min.js"></script>

  

  
    
<script src="https://cdn.jsdelivr.net/npm/vue@2.6.11/dist/vue.min.js"></script>

  
  
  <body>
    <!-- 预加载动画 -->
    <!-- 页面预加载动画 -->

<div id='loader'>
  <link rel="stylesheet" href="/js/loaded/index.css" >
  <div class="loading-left-bg"></div>
  <div class="loading-right-bg"></div>
  <div class="spinner-box">
    <div class="configure-border-1">
      <div class="configure-core"></div>
    </div>
    <div class="configure-border-2">
      <div class="configure-core"></div>
    </div>
    <div class="loading-word">loading...</div>
  </div>
</div>

<script>
  var endLoading = function () {
    document.body.style.overflow = 'auto';
    document.getElementById('loader').classList.add("loading");
  }
  window.addEventListener('DOMContentLoaded',endLoading);
  
</script>

    
    <!-- 判断是否为暗黑风格 -->
    <!-- 判断是否为黑夜模式 -->
<script>
  let isDark = JSON.parse(localStorage.getItem('dark')) || JSON.parse('false');

  if (isDark) {
    $(document.body).addClass('darkModel');
  }
</script>

    <!-- 需要在上面加载的js -->
    <script>
  function loadScript(src, cb) {
    return new Promise(resolve => {
      setTimeout(function () {
        var HEAD = document.getElementsByTagName("head")[0] || document.documentElement;
        var script = document.createElement("script");
        script.setAttribute("type", "text/javascript");
        if (cb) {
          if (JSON.stringify(cb)) {
            for (let p in cb) {
              if (p == "onload") {
                script[p] = () => {
                  cb[p]()
                  resolve()
                }
              } else {
                script[p] = cb[p]
                script.onload = resolve
              }
            }
          } else {
            script.onload = () => {
              cb()
              resolve()
            };
          }
        } else {
          script.onload = resolve
        }
        script.setAttribute("src", src);
        HEAD.appendChild(script);
      });
    });
  }

  //https://github.com/filamentgroup/loadCSS
  var loadCSS = function (href, before, media, attributes) {
    return new Promise(resolve => {
      setTimeout(function () {
        var link = document.createElement('link');
        link.rel = "stylesheet";
        link.href = src;
        link.onload = resolve;
        document.getElementsByTagName("head")[0].appendChild(link);
      });
    });
  };

</script> 

<!-- 轮播图所需要的js -->
<script src="/js/swiper/swiper.min.js"></script>
<script src="/js/swiper/vue-awesome-swiper.js"></script>
<script src="/js/swiper/swiper.animate1.0.3.min.js"></script>

<script type="text/javascript">
  Vue.use(window.VueAwesomeSwiper)
</script>


  <script src="/js/vue-typed-js/index.js"></script>


<!-- 首页的公告滚动插件的js需要重新加载 -->
<script src="/js/vue-seamless-scroll/index.js"></script>

<!-- 打字机效果js -->
<script src="https://cdn.jsdelivr.net/npm/typed.js@2.0.11"></script>


    <div id="safearea">
      <main class="main" id="pjax-container">
        <!-- 头部导航 -->
        
<header class="header   " 
  id="navHeader"
  style="position: fixed;
  left: 0; top: 0; z-index: 10;width: 100%;"
>
  <div class="header-content">
    <div class="bars">
      <div id="appDrawer" class="sidebar-image">
  <div class="drawer-box-icon">
    <i class="fas fa-bars" aria-hidden="true" @click="showDialogDrawer"></i>
  </div>
  
  <transition name="fade">
    <div class="drawer-box_mask" v-cloak style="display: none;" v-show="visible" @click.self="cancelDialogDrawer">
    </div>
  </transition>
  <div class="drawer-box" :class="{'active': visible}">
    <div class="drawer-box-head bg-color">
      <img class="drawer-box-head_logo lazyload placeholder" src="/medias/logo.png" class="lazyload placeholder" data-srcset="/medias/logo.png" srcset="https://img2.baidu.com/it/u=2037979560,2772131037&fm=26&fmt=auto&gp=0.jpg" alt="logo">
      <h3 class="drawer-box-head_title">BigBoss</h3>
      <h5 class="drawer-box-head_desc">千磨万击还坚劲，任尔东西南北风</h5>
    </div>
    
    <div class="drawer-box-content">
      <ul class="drawer-box-content_menu">
        
          
            <li class="drawer-box-content_item" style="position: relative;">
              
                <a href="/" class="drawer-menu-item-link">
                  
                    <i class="fas fa-home" aria-hidden="true"></i>
                  
                  <span class="name">主页</span>
                </a>
              
            </li>
          
            <li class="drawer-box-content_item" style="position: relative;">
              
                <a href="/archives" class="drawer-menu-item-link">
                  
                    <i class="fas fa-archive" aria-hidden="true"></i>
                  
                  <span class="name">归档</span>
                </a>
              
            </li>
          
            <li class="drawer-box-content_item" style="position: relative;">
              
                <a href="/tags" class="drawer-menu-item-link">
                  
                    <i class="fas fa-tags" aria-hidden="true"></i>
                  
                  <span class="name">标签</span>
                </a>
              
            </li>
          
            <li class="drawer-box-content_item" style="position: relative;">
              
                <a href="/categories" class="drawer-menu-item-link">
                  
                    <i class="fas fa-bookmark" aria-hidden="true"></i>
                  
                  <span class="name">分类</span>
                </a>
              
            </li>
          
            <li class="drawer-box-content_item" style="position: relative;">
              
                <a href="/about" class="drawer-menu-item-link">
                  
                    <i class="fas fa-user" aria-hidden="true"></i>
                  
                  <span class="name">关于</span>
                </a>
              
            </li>
          
            <li class="drawer-box-content_item" style="position: relative;">
              
                <a href="/friends" class="drawer-menu-item-link">
                  
                  <span class="name">友情链接</span>
                </a>
              
            </li>
          
            <li class="drawer-box-content_item" style="position: relative;">
              
                <a href="javascript:;" class="drawer-menu-item-link has-children" @click="openOrCloseMenu(6)">
                  <span>
                    
                      <i class="fas fa-link"></i>
                    
                    <span class="name">更多</span>
                  </span>
                  <i class="fas fa-chevron-left arrow " :class="{'icon-rotate': isOpen(6)}" aria-hidden="true"></i>
                </a>
                <ul class="drawer-sub-menu" v-if="isOpen(6)">
                  
                  <li>
                    <a href="/gallery">
                      
                      <i class="fas fa-music" style="margin-top: -20px;"></i>
                      
                      <span>图库</span>
                    </a>
                  </li>
                  
                  <li>
                    <a href="/huoban">
                      
                      <i class="fas fa-film" style="margin-top: -20px;"></i>
                      
                      <span>友情连接</span>
                    </a>
                  </li>
                  
                  <li>
                    <a href="/me">
                      
                      <i class="fas fa-film" style="margin-top: -20px;"></i>
                      
                      <span>关于我</span>
                    </a>
                  </li>
                  
                  <li>
                    <a target="_blank" rel="noopener" href="http://baidu.com">
                      
                      <i class="fas fa-film" style="margin-top: -20px;"></i>
                      
                      <span>百度</span>
                    </a>
                  </li>
                  
                </ul>
              
            </li>
          
        
        
          <li class="drawer-box-content_item">
            <a target="_blank" rel="noopener" href="https://github.com/geng-chao">
              <i class="fas fa-github" aria-hidden="true"></i>
              <span>Github</span>
            </a>
          </li>
        
      </ul>
    </div>
  </div>
</div>

<script>
  var body = document.body || document.documentElement || window;
  var vm = new Vue({
    el: '#appDrawer',
    data: {
      visible: false,
      top: 0,
      openArr: [],
    },
    computed: {
    },
    mounted() {
    },
    methods: {
      isOpen(index) {
        if (this.openArr.includes(index)) {
          return true;
        } else {
          return false;
        }
      },
      openOrCloseMenu(curIndex) {
        const index = this.openArr.indexOf(curIndex);
        if (index !== -1) {
          this.openArr.splice(index, 1);
        } else {
          this.openArr.push(curIndex);
        }
      },
      showDialogDrawer() {
        this.visible = true;
        // 防止页面滚动，只能让弹框滚动
        this.top = $(document).scrollTop()
        body.style.cssText = 'width: 100%; height: 100%;overflow: hidden;';
      },
      cancelDialogDrawer() {
        this.visible = false;
        body.removeAttribute('style');
        $(document).scrollTop(this.top)
      }
    },
    created() {}
  })
</script>

    </div>
    <div class="blog-title" id="author-avatar">
      
        <div class="avatar">
          <img src="/medias/logo.png" class="lazyload placeholder" data-srcset="/medias/logo.png" srcset="https://img2.baidu.com/it/u=2037979560,2772131037&fm=26&fmt=auto&gp=0.jpg" alt="logo">
        </div>
      
      <a href="/" class="logo">BigBoss</a>
    </div>
    <nav class="navbar">
      <ul class="menu">
        
          
            <li class="menu-item" style="position: relative;">
              
                <a href="/" class="menu-item-link" title="主页">
                  
                    <i class="fas fa-home" aria-hidden="true"></i>
                  
                  <span class="name">主页</span>
                </a>
              
            </li>
          
            <li class="menu-item" style="position: relative;">
              
                <a href="/archives" class="menu-item-link" title="归档">
                  
                    <i class="fas fa-archive" aria-hidden="true"></i>
                  
                  <span class="name">归档</span>
                </a>
              
            </li>
          
            <li class="menu-item" style="position: relative;">
              
                <a href="/tags" class="menu-item-link" title="标签">
                  
                    <i class="fas fa-tags" aria-hidden="true"></i>
                  
                  <span class="name">标签</span>
                </a>
              
            </li>
          
            <li class="menu-item" style="position: relative;">
              
                <a href="/categories" class="menu-item-link" title="分类">
                  
                    <i class="fas fa-bookmark" aria-hidden="true"></i>
                  
                  <span class="name">分类</span>
                </a>
              
            </li>
          
            <li class="menu-item" style="position: relative;">
              
                <a href="/about" class="menu-item-link" title="关于">
                  
                    <i class="fas fa-user" aria-hidden="true"></i>
                  
                  <span class="name">关于</span>
                </a>
              
            </li>
          
            <li class="menu-item" style="position: relative;">
              
                <a href="/friends" class="menu-item-link" title="友情链接">
                  
                  <span class="name">友情链接</span>
                </a>
              
            </li>
          
            <li class="menu-item" style="position: relative;">
              
                <a href="javascript:;" class="menu-item-link" title="更多">
                  
                    <i class="fas fa-link"></i>
                  
                  <span class="name">更多</span>
                  <i class="fas fa-chevron-down arrow" aria-hidden="true"></i>
                </a>
                <ul class="sub-menu">
                  
                  <li>
                    <a href="/gallery">
                      
                      <i class="fas fa-music" style="margin-top: -20px;"></i>
                      
                      <span>图库</span>
                    </a>
                  </li>
                  
                  <li>
                    <a href="/huoban">
                      
                      <i class="fas fa-film" style="margin-top: -20px;"></i>
                      
                      <span>友情连接</span>
                    </a>
                  </li>
                  
                  <li>
                    <a href="/me">
                      
                      <i class="fas fa-film" style="margin-top: -20px;"></i>
                      
                      <span>关于我</span>
                    </a>
                  </li>
                  
                  <li>
                    <a target="_blank" rel="noopener" href="http://baidu.com">
                      
                      <i class="fas fa-film" style="margin-top: -20px;"></i>
                      
                      <span>百度</span>
                    </a>
                  </li>
                  
                </ul>
              
            </li>
          
        
      </ul>
      
      
        <div id="appSearch">
  <div class="search"  @click="showDialog()"><i class="fas fa-search" aria-hidden="true"></i></div>
  <transition name="fade">
    <div class="message-box_wrapper" style="display: none;" v-cloak v-show="dialogVisible" @click.self="cancelDialogVisible()">
      <div class="message-box animated bounceInDown">
        <h2>
          <span>
            <i class="fas fa-search" aria-hidden="true"></i>
            <span class="title">Search</span>
          </span>
          <i class="fas fa-times close" pointer style="float:right;" aria-hidden="true" @click.self="cancelDialogVisible()"></i>
        </h2>
        <form class="site-search-form">
          <input type="text"
            placeholder="Please enter keywords"
            id="local-search-input" 
            @click="getSearchFile()"
            class="st-search-input"
            v-model="searchInput"
          />
        </form>
        <div class="result-wrapper">
          <div id="local-search-result" class="local-search-result-cls"></div>
        </div>
      </div>
    </div>
  </transition>
</div>
<script src="/js/local_search.js"></script>
<script>
  var body = document.body || document.documentElement || window;
  var vm = new Vue({
    el: '#appSearch',
    data: {
      dialogVisible: false,
      searchInput: '',
      top: 0,
    },
    computed: {
    },
    mounted() {
      window.addEventListener('pjax:complete', () => {
        this.cancelDialogVisible();
      })
    },
    methods: {
      showDialog() {
        this.dialogVisible = true;
        // 防止页面滚动，只能让弹框滚动
        this.top = $(document).scrollTop()
        body.style.cssText = 'overflow: hidden;';
      },
      getSearchFile() {
        if (!this.searchInput) {
          getSearchFile("/search.xml");
        }
      },
      cancelDialogVisible() {
        this.dialogVisible = false;
        body.removeAttribute('style');
        $(document).scrollTop(this.top)
      },
    },
    created() {}
  })
</script>
<!-- 解决刷新页面闪烁问题，可以在元素上添加display: none, 或者用vue.extend方法，详情：https://blog.csdn.net/qq_31393401/article/details/81017912 -->
<!-- 下面是搜索基本写法 -->
<!-- <script type="text/javascript" id="local.search.active">
  var inputArea = document.querySelector("#local-search-input");
  inputArea.onclick   = function(){ getSearchFile(); this.onclick = null }
  inputArea.onkeydown = function(){ if(event.keyCode == 13) return false }
</script> -->

      

    </nav>
  </div>
  
    <a target="_blank" rel="noopener" href="https://github.com/geng-chao" class="github-corner color-primary" aria-label="View source on GitHub"><svg width="60" height="60" viewBox="0 0 250 250" style="fill:#fff; position: absolute; top: 0; border: 0; right: 0;" aria-hidden="true"><path d="M0,0 L115,115 L130,115 L142,142 L250,250 L250,0 Z"></path><path d="M128.3,109.0 C113.8,99.7 119.0,89.6 119.0,89.6 C122.0,82.7 120.5,78.6 120.5,78.6 C119.2,72.0 123.4,76.3 123.4,76.3 C127.3,80.9 125.5,87.3 125.5,87.3 C122.9,97.6 130.6,101.9 134.4,103.2" fill="currentColor" style="transform-origin: 130px 106px;" class="octo-arm"></path><path d="M115.0,115.0 C114.9,115.1 118.7,116.5 119.8,115.4 L133.7,101.6 C136.9,99.2 139.9,98.4 142.2,98.6 C133.8,88.0 127.5,74.4 143.8,58.0 C148.5,53.4 154.0,51.2 159.7,51.0 C160.3,49.4 163.2,43.6 171.4,40.1 C171.4,40.1 176.1,42.5 178.8,56.2 C183.1,58.6 187.2,61.8 190.9,65.4 C194.5,69.0 197.7,73.2 200.1,77.6 C213.8,80.2 216.3,84.9 216.3,84.9 C212.7,93.1 206.9,96.0 205.4,96.6 C205.1,102.4 203.0,107.8 198.3,112.5 C181.9,128.9 168.3,122.5 157.7,114.1 C157.9,116.9 156.7,120.9 152.7,124.9 L141.0,136.5 C139.8,137.7 141.6,141.9 141.8,141.8 Z" fill="currentColor" class="octo-body"></path></svg></a><style>.github-corner:hover .octo-arm{animation:octocat-wave 560ms ease-in-out}@keyframes octocat-wave{0%,100%{transform:rotate(0)}20%,60%{transform:rotate(-25deg)}40%,80%{transform:rotate(10deg)}}@media (max-width:500px){.github-corner:hover .octo-arm{animation:none}.github-corner .octo-arm{animation:octocat-wave 560ms ease-in-out}}</style>
  
  
    <div id="he-plugin-simple"></div>
    <script>
      WIDGET = {
        CONFIG: {
          "modules": "012",
          "background": 5,
          "tmpColor": "4A4A4A",
          "tmpSize": 16,
          "cityColor": "4A4A4A",
          "citySize": 16,
          "aqiSize": 16,
          "weatherIconSize": 24,
          "alertIconSize": 18,
          "padding": "10px 10px 10px 10px",
          "shadow": "1",
          "language": "auto",
          "borderRadius": 5,
          "fixed": "false",
          "vertical": "middle",
          "horizontal": "center",
          "key": "2784dd3fcb1e4f0f9a9b579bf69641f2"
        }
      }
    </script>
    <script src="https://widget.qweather.net/simple/static/js/he-simple-common.js?v=2.0"></script> 
    
</header>
        <!-- 内容区域 -->
        
 <!-- prismjs 代码高亮 -->
 
    
    <link href="/js/prism/prism-line-numbers.css" rel="stylesheet">
    
        <link href="/js/prism/prism-dark.min.css" rel="stylesheet">
    

    <style>
        pre[class*="language-"] {
            overflow-y: hidden;
        }
        .line-numbers .line-numbers-rows {
            border: none;
        }
    </style>
  


<div class="bg-dark-floor" style="position: fixed;left: 0;top: 0;width: 100%;height: 100%;z-index: -1;"></div>


  <!-- 文章详情页顶部图片和标题 -->




<div class="post-detail-header" id="thumbnail_canvas" style="background-repeat: no-repeat; background-size: cover; 
  background-position: center center;position: relative;background-image:url('/medias/9.jpg')">
  <div class="post-detail-header-mask"></div>
  <canvas id="header_canvas"style="position:absolute;bottom:0;pointer-events:none;"></canvas>
  
  <div class="post-detail-header_info-box">
    <div class="title-box">
      <span class="title">
        SQL语句大全，所有的SQL都在这里
      </span>
    </div>
    
    
      
        <span class="post-detail-header_date">
          <i class="fas fa-calendar"></i> Published in：2020-03-06 |
        </span>
      

      
        <span class="post-detail-header_categories">
          <i class="iconfont iconbookmark1"></i> category：
          
            <a href="/categories/%E6%8A%80%E6%9C%AF/" class="post-detail-header_category">
              技术
            </a>
          
        </span>
      

      
        <div class="post-detail-header_wordcount">
          <span class="totalcount">
            <i class="fas fa-file-text-o"></i> Words: 6.5k |
          </span>
  
          <span class="min2read">
            <i class="fas fa-clock"></i> Reading time: 27min |
          </span>
  
          
            <span class="reading">
              <i class="fas fa-eye"></i> reading：<span id="busuanzi_value_page_pv"></span>
            </span>
          
        </div>
      
    
  </div>
  
  
    <script src="/js/bubble/bubble.js"></script>
  
</div>





<div class="row justify-position" 
  style="padding-top: 0px;">
  <div class="main-content">
    <article class="post post-detail">
      <div class="post-content">
        <p>​                   <font face="黑体" color=red>一、基础</font></p>
<p><strong>1、说明：创建数据库CREATE DATABASE database-name</strong><br>2、说明：删除数据库drop database dbname                                        3、说明：备份sql server— 创建 备份数据的 deviceUSE masterEXEC sp_addumpdevice ‘disk’, ‘testBack’, ‘c:\mssql7backup\MyNwind_1.dat’— 开始 备份BACKUP DATABASE pubs TO testBack </p>
<p><strong>4、说明：创建新表create table tabname(col1 type1 [not null][primary key],col2 type2 [not null],..)</strong></p>
<p><strong>根据已有的表创建新表： **<br><strong>A：create table tab_new like tab_old (使用旧表创建新表)</strong><br><strong>B<strong><strong>：create table tab_new as select col1,col2… from tab_old definition only</strong></strong>5、说明：删除新表drop table tabname</strong><br><strong>6、说明：增加一个列Alter table tabname add column col type****注</strong>：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。<br><strong>7、说明：添加主键</strong>： <strong>Alter table tabname add primary key(col) <strong>说明：删除主键：</strong> Alter table tabname drop primary key(col) *<em><strong>8、说明：创建索引</strong>：</em>*create [unique] index idxname on tabname(col….) <strong>删除索引：</strong>drop index idxname</strong><br>注：索引是不可更改的，想更改必须删除重新建。<br><strong>9****、说明：创建视图</strong>：create view viewname as select statement<br><strong>删除视图</strong>：drop view viewname<br><strong>10****、说明：几个简单的基本的sql语句选择：</strong>select * from table1 where 范围<br><strong>插入：</strong>insert into table1(field1,field2) values(value1,value2)<br><strong>删除：</strong>delete from table1 where 范围</strong>更新**：update table1 set field1&#x3D;value1 where 范围<br><strong>查找</strong>：select * from table1 where field1 like ’%value1%’ —like的语法很精妙，查资料!<br><strong>排序</strong>：select * from table1 order by field1,field2 [desc]<br><strong>总数</strong>：select count as totalcount from table1<br><strong>求和</strong>：select sum(field1) as sumvalue from table1<br><strong>平均</strong>：select avg(field1) as avgvalue from table1<br><strong>最大</strong>：select max(field1) as maxvalue from table1<br><strong>最小</strong>：select min(field1) as minvalue from table1<br><strong>11、说明：几个高级查询运算词A： UNION 运算符</strong><br>UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 </p>
<p><strong>B： EXCEPT 运算符 EXCEPT</strong>运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。</p>
<p><strong>C： INTERSECT 运算符INTERSECT</strong>运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 <strong>ALL</strong>随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。 </p>
<p><strong>注：</strong>使用运算词的几个查询结果行必须是一致的。<br><strong>12****、说明：使用外连接 <strong>A、</strong>left （outer） join</strong>：<br>左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。<br>SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a &#x3D; b.c<br>**B：right （outer） join: **<br>右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。<br><strong>C：full&#x2F;cross （outer） join</strong>：<br>全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。<br>*<em>12</em>***、分组:Group by:   **一张表，一旦分组 完成后，查询后只能得到组相关的信息。</p>
<p>​    <strong>组相关的信息：</strong>（统计信息） count,sum,max,min,avg ** **<strong>分组的标准)  ****  在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据   在selecte统计函数中的字段，不能和普通的字段放在一起；</strong></p>
<p><strong>13、对数据库进行操作：   分离数据库</strong>：sp_detach_db;附加数据库<strong>：</strong>sp_attach_db 后接表明，附加需要完整的路径名<br>14.如何修改数据库的名称:<br>sp_renamedb ‘old_name’, ‘new_name’</p>
<p>​                  <font face="黑体" color=red  size=6>二、提升</font></p>
<p><strong>1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)法一：</strong>select * into b from a where 1&lt;&gt;1（仅用于SQlServer）<strong>法二：</strong>select top 0 * into b from a<br>**2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)**insert into b(a, b, c) select d,e,f from b;</p>
<p>**3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)**insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件<br>例子：..from b in ‘“&amp;Server.MapPath(“.”)&amp;”\data.mdb” &amp;”‘ where..</p>
<p>**4、说明：子查询(表名1：a 表名2：b)**select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)</p>
<p><strong>5、说明：显示文章、提交人和最后回复时间</strong>select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title&#x3D;a.title) b</p>
<p>**6、说明：外连接查询(表名1：a 表名2：b)**select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a &#x3D; b.c</p>
<p>**7、说明：在线视图查询(表名1：a )**select * from (SELECT a,b,c FROM a) T where t.a &gt; 1;</p>
<p><strong>8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括</strong>select * from table1 where time between time1 and time2<br>select a,b,c, from table1 where a not between 数值1 and 数值2</p>
<p><strong>9、说明：in 的使用方法</strong>select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)</p>
<p>**10、说明：两张关联表，删除主表中已经在副表中没有的信息 **delete from table1 where not exists ( select * from table2 where table1.field1&#x3D;table2.field1 )</p>
<p><strong>11、说明：四表联查问题：</strong>select * from a left inner join b on a.a&#x3D;b.b right inner join c on a.a&#x3D;c.c inner join d on a.a&#x3D;d.d where …..</p>
<p>**12、说明：日程安排提前五分钟提醒 **SQL: select * from 日程安排 where datediff(‘minute’,f开始时间,getdate())&gt;5</p>
<p><strong>13<strong><strong>、说明：一条</strong></strong>sql *<em><strong>语句搞定数据库分页</strong>select top 10 b.</em> from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 &#x3D; a.主键字段 order by a.排序字段**具体实现：</strong>关于数据库分页：</p>
<p>  declare @start int,@end int</p>
<p>  @sql  nvarchar(600)</p>
<p>  set @sql&#x3D;’select top’+str(@end-@start+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid&gt;-1)’</p>
<p>  exec sp_executesql @sql</p>
<p><strong>注意：在<strong><strong>top</strong></strong>后不能直接跟一个变量，所以在实际应用中只有这样的进行特殊的处理。<strong><strong>Rid</strong></strong>为一个标识列，如果<strong><strong>top</strong></strong>后还有具体的字段，这样做是非常有好处的。因为这样可以避免**</strong> top<strong><strong>的字段如果是逻辑索引的，查询的结果后实际表中的不一致（</strong></strong>逻辑索引中的数据有可能和数据表中的不一致，而查询时如果处在索引则首先查询索引**<strong>）</strong></p>
<p><strong>14、说明：前10条记录</strong>select top 10 * form table1 where 范围</p>
<p>**15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)**select a,b,c from tablename ta where a&#x3D;(select max(a) from tablename tb where tb.b&#x3D;ta.b)</p>
<p><strong>16、说明：包括所有在 TableA中但不在 TableB和TableC中的行并消除所有重复行而派生出一个结果表</strong>(select a from tableA ) except (select a from tableB) except (select a from tableC)</p>
<p><strong>17、说明：随机取出10条数据</strong>select top 10 * from tablename order by <strong>newid()</strong></p>
<p><strong>18、说明：随机选择记录</strong>select newid()</p>
<p>**19、说明：删除重复记录1),**delete from tablename where id not in (select max(id) from tablename group by col1,col2,…)<br><strong>2)</strong>,select distinct * into temp from tablename<br>  delete from tablename<br>  insert into tablename select * from temp<br><strong>评价： 这种操作牵连大量的数据的移动，这种做法不适合大容量但数据操作3),例如：在一个外部表中导入数据，由于某些原因第一次只导入了一部分，但很难判断具体位置，这样只有在下一次全部导入，这样也就产生好多重复的字段，怎样删除重复字段</strong></p>
<p>alter table tablename<br>–添加一个自增列<br>add  column_b int identity(1,1)<br> delete from tablename where column_b not in(<br>select max(column_b)  from tablename group by column1,column2,…)<br>alter table tablename drop column column_b</p>
<p><strong>20、说明：列出数据库里所有的表名</strong>select name from sysobjects where type&#x3D;’U’ &#x2F;&#x2F; U代表用户</p>
<p><strong>21、说明：列出表里的所有的列名</strong>select name from syscolumns where id&#x3D;object_id(‘TableName’)</p>
<p><strong>22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。</strong>select type,sum(case vender when ‘A’ then pcs else 0 end),sum(case vender when ‘C’ then pcs else 0 end),sum(case vender when ‘B’ then pcs else 0 end) FROM tablename group by type<br><strong>显示结果：type vender pcs</strong>电脑 A 1<br>电脑 A 1<br>光盘 B 2<br>光盘 A 2<br>手机 B 3<br>手机 C 3</p>
<p><strong>23、说明：初始化表table1</strong></p>
<p>TRUNCATE TABLE table1</p>
<p><strong>24、说明：选择从10到15的记录</strong>select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc</p>
<p>​     <font face="黑体" color=red  size=6>三、技巧</font></p>
<p><strong>1、1&#x3D;1，1&#x3D;2的使用，在SQL语句组合时用的较多</strong></p>
<p><strong>“where 1&#x3D;1” 是表示选择全部    “where 1&#x3D;2”全部不选，</strong><br>如：<br>if @strWhere !&#x3D;’’<br>begin<br>set @strSQL &#x3D; ‘select count(<em>) as Total from [‘ + @tblName + ‘] where ‘ + @strWhere<br>end<br>else<br>begin<br>set @strSQL &#x3D; ‘select count(</em>) as Total from [‘ + @tblName + ‘]’<br>end</p>
<p><strong>我们可以直接写成</strong></p>
<p>错误！未找到目录项。<strong>set @strSQL &#x3D; ‘select count(*) as Total from [‘ + @tblName + ‘] where 1&#x3D;1 安定 ‘+ @strWhere 2、收缩数据库</strong>–重建索引<br>DBCC REINDEX<br>DBCC INDEXDEFRAG<br>–收缩数据和日志<br>DBCC SHRINKDB<br>DBCC SHRINKFILE</p>
<p><strong>3、压缩数据库</strong>dbcc shrinkdatabase(dbname)</p>
<p><strong>4、转移数据库给新用户以已存在用户权限</strong>exec sp_change_users_login ‘update_one’,’newname’,’oldname’<br>go</p>
<p><strong>5、检查备份集</strong>RESTORE VERIFYONLY from disk&#x3D;’E:\dvbbs.bak’</p>
<p><strong>6、修复数据库</strong>ALTER DATABASE [dvbbs] SET SINGLE_USER<br>GO<br>DBCC CHECKDB(‘dvbbs’,repair_allow_data_loss) WITH TABLOCK<br>GO<br>ALTER DATABASE [dvbbs] SET MULTI_USER<br>GO</p>
<p><strong>7、日志清除</strong>SET NOCOUNT ON<br>DECLARE @LogicalFileName sysname,<br> @MaxMinutes INT,<br> @NewSize INT</p>
<p>USE tablename – 要操作的数据库名<br>SELECT  @LogicalFileName &#x3D; ‘tablename_log’, – 日志文件名<br>@MaxMinutes &#x3D; 10, – Limit on time allowed to wrap log.<br> @NewSize &#x3D; 1  – 你想设定的日志文件的大小(M)</p>
<p>Setup &#x2F; initialize<br>DECLARE @OriginalSize int<br>SELECT @OriginalSize &#x3D; size<br> FROM sysfiles<br> WHERE name &#x3D; @LogicalFileName<br>SELECT ‘Original Size of ‘ + db_name() + ‘ LOG is ‘ +<br> CONVERT(VARCHAR(30),@OriginalSize) + ‘ 8K pages or ‘ +<br> CONVERT(VARCHAR(30),(@OriginalSize*8&#x2F;1024)) + ‘MB’<br> FROM sysfiles<br> WHERE name &#x3D; @LogicalFileName<br>CREATE TABLE DummyTrans<br> (DummyColumn char (8000) not null)</p>
<p>DECLARE @Counter    INT,<br> @StartTime DATETIME,<br> @TruncLog   VARCHAR(255)<br>SELECT @StartTime &#x3D; GETDATE(),<br> @TruncLog &#x3D; ‘BACKUP LOG ‘ + db_name() + ‘ WITH TRUNCATE_ONLY’</p>
<p>DBCC SHRINKFILE (@LogicalFileName, @NewSize)<br>EXEC (@TruncLog)<br>– Wrap the log if necessary.<br>WHILE @MaxMinutes &gt; DATEDIFF (mi, @StartTime, GETDATE()) – time has not expired<br> AND @OriginalSize &#x3D; (SELECT size FROM sysfiles WHERE name &#x3D; @LogicalFileName)<br> AND (@OriginalSize * 8 &#x2F;1024) &gt; @NewSize<br> BEGIN – Outer loop.<br>SELECT @Counter &#x3D; 0<br> WHILE   ((@Counter &lt; @OriginalSize &#x2F; 16) AND (@Counter &lt; 50000))<br> BEGIN – update<br> INSERT DummyTrans VALUES (‘Fill Log’) DELETE DummyTrans<br> SELECT @Counter &#x3D; @Counter + 1<br> END<br> EXEC (@TruncLog)<br> END<br>SELECT ‘Final Size of ‘ + db_name() + ‘ LOG is ‘ +<br> CONVERT(VARCHAR(30),size) + ‘ 8K pages or ‘ +<br> CONVERT(VARCHAR(30),(size*8&#x2F;1024)) + ‘MB’<br> FROM sysfiles<br> WHERE name &#x3D; @LogicalFileName<br>DROP TABLE DummyTrans<br>SET NOCOUNT OFF</p>
<p><strong>8、说明：更改某个表</strong>exec sp_changeobjectowner ‘tablename’,’dbo’</p>
<p><strong>9、存储更改全部表</strong></p>
<p>CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch<br>@OldOwner as NVARCHAR(128),<br>@NewOwner as NVARCHAR(128)<br>AS</p>
<p>DECLARE @Name    as NVARCHAR(128)<br>DECLARE @Owner   as NVARCHAR(128)<br>DECLARE @OwnerName   as NVARCHAR(128)</p>
<p>DECLARE curObject CURSOR FOR<br>select ‘Name’    &#x3D; name,<br>   ‘Owner’    &#x3D; user_name(uid)<br>from sysobjects<br>where user_name(uid)&#x3D;@OldOwner<br>order by name</p>
<p>OPEN   curObject<br>FETCH NEXT FROM curObject INTO @Name, @Owner<br>WHILE(@@FETCH_STATUS&#x3D;0)<br>BEGIN<br>if @Owner&#x3D;@OldOwner<br>begin<br>   set @OwnerName &#x3D; @OldOwner + ‘.’ + rtrim(@Name)<br>   exec sp_changeobjectowner @OwnerName, @NewOwner<br>end<br>– select @name,@NewOwner,@OldOwner</p>
<p>FETCH NEXT FROM curObject INTO @Name, @Owner<br>END</p>
<p>close curObject<br>deallocate curObject<br>GO</p>
<p><strong>10、SQL SERVER中直接循环写入数据</strong>declare @i int<br>set @i&#x3D;1<br>while @i&lt;30<br>begin<br>​    insert into test (userid) values(@i)<br>​    set @i&#x3D;@i+1<br>end<br><strong>案例<strong><strong>：</strong></strong>有如下表，要求就裱中所有沒有及格的成績，在每次增長0.1的基礎上，使他們剛好及格:</strong></p>
<p>​    Name     score</p>
<p>​    Zhangshan   80</p>
<p>​    Lishi       59</p>
<p>​    Wangwu      50</p>
<p>​    Songquan    69</p>
<p><strong>while((select <strong><strong>min</strong></strong>(score) from tb_table)&lt;<strong><strong>60</strong></strong>)</strong></p>
<p><strong>begin</strong></p>
<p>**update tb_table set score ****&#x3D;****score***<strong>1.01</strong></p>
<p><strong>where score</strong><strong>&lt;60</strong></p>
<p><strong>if  (select <strong><strong>min</strong></strong>(score) from tb_table)****&gt;60</strong></p>
<p>**  break**</p>
<p>** else**</p>
<p>**    continue**</p>
<p><strong>end</strong></p>
<p>  <font face="黑体" color=red  size=6>数据开发-经典</font></p>
<p><strong>1.按姓氏笔画排序:</strong><br>Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as &#x2F;&#x2F;从少到多</p>
<p>**2.数据库加密:**select encrypt(‘原始密码’)<br>select pwdencrypt(‘原始密码’)<br>select pwdcompare(‘原始密码’,’加密后密码’) &#x3D; 1–相同；否则不相同 encrypt(‘原始密码’)<br>select pwdencrypt(‘原始密码’)<br>select pwdcompare(‘原始密码’,’加密后密码’) &#x3D; 1–相同；否则不相同</p>
<p>**3.取回表中字段:**declare @list varchar(1000),<br>@sql nvarchar(1000)<br>select @list&#x3D;@list+’,’+b.name from sysobjects a,syscolumns b where a.id&#x3D;b.id and a.name&#x3D;’表A’<br>set @sql&#x3D;’select ‘+right(@list,len(@list)-1)+’ from 表A’<br>exec (@sql)</p>
<p><strong>4.查看硬盘分区:EXEC master..xp_fixeddrives</strong></p>
<p>**5.比较A,B表是否相等:*<em>if (select checksum_agg(binary_checksum(</em>)) from A)<br>​     &#x3D;<br>​    (select checksum_agg(binary_checksum(*)) from B)<br>print ‘相等’<br>else<br>print ‘不相等’</p>
<p>**6.杀掉所有的事件探察器进程:**DECLARE hcforeach CURSOR GLOBAL FOR SELECT ‘kill ‘+RTRIM(spid) FROM master.dbo.sysprocesses<br>WHERE program_name IN(‘SQL profiler’,N’SQL 事件探查器’)<br>EXEC sp_msforeach_worker ‘?’</p>
<p>*<em>7.<strong><strong>记录搜索</strong></strong>:<strong><strong>开头到</strong></strong>N</em><em><strong>条记录</strong>Select Top N * From 表——————————-<br><strong>N</strong></em><em>到</em><em><strong>M</strong></em><em>条记录</em><em>**(<strong><strong>要有主索引</strong></strong>ID)*<em>Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID   Desc<br>-———————————<br><strong>N</strong></em>*到结尾记录**Select Top N * From 表 Order by ID Desc<br><strong>案例</strong></em><em>例如1：一张表有一万多条记录，表的第一个字段 RecID 是自增长字段， 写一个SQL语句， 找出表的第</em><em><strong>31</strong></em><em>到第</em><em><strong>40</strong></em><em>个记录。</em>*</p>
<p> select top 10 recid from A where recid not  in(select top 30 recid from A)</p>
<p>分析：如果这样写会产生某些问题，如果recid在表中存在逻辑索引。</p>
<p>​    select top 10 recid from A where……是从索引中查找，而后面的select top 30 recid from A则在数据表中查找，这样由于索引中的顺序有可能和数据表中的不一致，这样就导致查询到的不是本来的欲得到的数据。</p>
<p><strong>解决方案</strong></p>
<p>*<em>1，</em>***用order by **select top 30 recid from A order by ricid <strong>如果该字段不是自增长，就会出现问题</strong></p>
<p><strong>2，****在那个子查询中也加条件：</strong>select top 30 recid from A where recid&gt;-1</p>
<p>*<em>例2</em>***：查询表中的最后以条记录，并不知道这个表共有多少数据,**<strong>以及表结构。set @s &#x3D; ‘select top 1 * from T   where pid not in (select top ‘ + str(@count-1) + ‘ pid  from  T)’</strong></p>
<p><strong>print @s      exec  sp_executesql  @s</strong></p>
<p><strong>9：获取当前数据库中的所有用户表</strong><br>select Name from sysobjects where xtype&#x3D;’u’ and status&gt;&#x3D;0</p>
<p><strong>10：获取某一个表的所有字段</strong>select name from syscolumns where id&#x3D;object_id(‘表名’)</p>
<p>select name from syscolumns where id in (select id from sysobjects where type &#x3D; ‘u’ and name &#x3D; ‘表名’)</p>
<p>两种方式的效果相同</p>
<p><strong>11：查看与某一个表相关的视图、存储过程、函数</strong>select a.* from sysobjects a, syscomments b where a.id &#x3D; b.id and b.text like ‘%表名%’</p>
<p><strong>12：查看当前数据库中所有存储过程</strong>select name as 存储过程名称 from sysobjects where xtype&#x3D;’P’</p>
<p><strong>13：查询用户创建的所有数据库</strong>select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name&#x3D;’sa’)<br>或者<br>select dbid, name AS DB_NAME from master..sysdatabases where sid &lt;&gt; 0x01</p>
<p><strong>14：查询某一个表的字段和数据类型</strong>select column_name,data_type from information_schema.columns<br>where table_name &#x3D; ‘表名’</p>
<p><strong>15****：不同服务器数据库之间的数据操作</strong></p>
<p>**–**<strong>创建链接服务器</strong></p>
<p>exec sp_addlinkedserver   ‘ITSV ‘, ‘ ‘, ‘SQLOLEDB ‘, ‘远程服务器名或ip地址 ‘</p>
<p>exec sp_addlinkedsrvlogin  ‘ITSV ‘, ‘false ‘,null, ‘用户名 ‘, ‘密码 ‘</p>
<p>–查询示例</p>
<p>select * from ITSV.数据库名.dbo.表名</p>
<p>–导入示例</p>
<p>select * into 表 from ITSV.数据库名.dbo.表名</p>
<p>**–**<strong>以后不再使用时删除链接服务器</strong></p>
<p>exec sp_dropserver  ‘ITSV ‘, ‘droplogins ‘</p>
<hr>
<p>**–**<strong>连接远程&#x2F;局域网数据</strong>(openrowset&#x2F;openquery&#x2F;opendatasource)</p>
<p>–1、openrowset</p>
<p>–查询示例</p>
<p>select * from openrowset( ‘SQLOLEDB ‘, ‘sql服务器名 ‘; ‘用户名 ‘; ‘密码 ‘,数据库名.dbo.表名)</p>
<p>–生成本地表</p>
<p>select * into 表 from openrowset( ‘SQLOLEDB ‘, ‘sql服务器名 ‘; ‘用户名 ‘; ‘密码 ‘,数据库名.dbo.表名)</p>
<p>–把本地表导入远程表</p>
<p>insert openrowset( ‘SQLOLEDB ‘, ‘sql服务器名 ‘; ‘用户名 ‘; ‘密码 ‘,数据库名.dbo.表名)</p>
<p>select *from 本地表</p>
<p>–更新本地表</p>
<p>update b</p>
<p>set b.列A&#x3D;a.列A</p>
<p> from openrowset( ‘SQLOLEDB ‘, ‘sql服务器名 ‘; ‘用户名 ‘; ‘密码 ‘,数据库名.dbo.表名)as a inner join 本地表 b</p>
<p>on a.column1&#x3D;b.column1</p>
<p>–openquery用法需要创建一个连接</p>
<p>–首先创建一个连接创建链接服务器</p>
<p>exec sp_addlinkedserver   ‘ITSV ‘, ‘ ‘, ‘SQLOLEDB ‘, ‘远程服务器名或ip地址 ‘</p>
<p>–查询</p>
<p>select *</p>
<p>FROM openquery(ITSV,  ‘SELECT *  FROM 数据库.dbo.表名 ‘)</p>
<p>–把本地表导入远程表</p>
<p>insert openquery(ITSV,  ‘SELECT *  FROM 数据库.dbo.表名 ‘)</p>
<p>select * from 本地表</p>
<p>–更新本地表</p>
<p>update b</p>
<p>set b.列B&#x3D;a.列B</p>
<p>FROM openquery(ITSV,  ‘SELECT * FROM 数据库.dbo.表名 ‘) as a </p>
<p>inner join 本地表 b on a.列A&#x3D;b.列A</p>
<p>–3、opendatasource&#x2F;openrowset</p>
<p>SELECT   *</p>
<p>FROM   opendatasource( ‘SQLOLEDB ‘,  ‘Data Source&#x3D;ip&#x2F;ServerName;User ID&#x3D;登陆名;Password&#x3D;密码 ‘ ).test.dbo.roy_ta</p>
<p>–把本地表导入远程表</p>
<p>insert opendatasource( ‘SQLOLEDB ‘,  ‘Data Source&#x3D;ip&#x2F;ServerName;User ID&#x3D;登陆名;Password&#x3D;密码 ‘).数据库.dbo.表名</p>
<p>select * from 本地表 </p>
<p>  <font face="黑体" color=red  size=6>SQL Server  基本函数</font></p>
<p>SQL Server基本函数</p>
<p>**1.字符串函数 **长度与分析用</p>
<p><strong>1,datalength</strong>(Char_expr) 返回字符串包含字符数,但不包含后面的空格<strong>2,substring</strong>(expression,start,length) 取子串，字符串的下标是从“1”，start为起始位置，length为字符串长度，实际应用中以len(expression)取得其长度<strong>3,right</strong>(char_expr,int_expr) 返回字符串右边第int_expr个字符，还用<strong>left</strong>于之相反<br>**4,isnull( <em><strong>check</strong></em>_***expression ***,** *replacement_value ***)**如果check_expression為空，則返回replacement_value的值，不為空，就返回check_expression字符操作类</p>
<p><strong>5,Sp_addtype</strong>自定義數據類型<br>例如：EXEC sp_addtype birthday, datetime, ‘NULL’</p>
<pre class="line-numbers language-none"><code class="language-none">6,set nocount &#123;on|off&#125;<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。SET NOCOUNT 设置是在执行或运行时设置，而不是在分析时设置。<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。

SET NOCOUNT 为 OFF 时，返回计数<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">常识<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none"> <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">在SQL查询中：from后最多可以跟多少张表或视图：256<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">在SQL语句中出现 Order by,查询时，先排序，后取<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">在SQL中，一个字段的最大容量是8000，而对于nvarchar(4000),由于nvarchar是Unicode码。  <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">        <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">SQLServer2000同步复制技术实现步骤<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">一、 预备工作<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">1.发布服务器,订阅服务器都创建一个同名的windows用户,并设置相同的密码,做为发布快照文件夹的有效访问用户<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--管理工具<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--计算机管理<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--用户和组<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--右键用户<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--新建用户<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--建立一个隶属于administrator组的登陆windows的用户（SynUser）<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">2.在发布服务器上,新建一个共享目录,做为发布的快照文件的存放目录,操作:<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">我的电脑--D:\ 新建一个目录,名为: PUB<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--右键这个新建的目录<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--属性--共享<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--选择&quot;共享该文件夹&quot;<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--通过&quot;权限&quot;按纽来设置具体的用户权限,保证第一步中创建的用户(SynUser) 具有对该文件夹的所有权限<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none"> <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--确定<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">3.设置SQL代理(SQLSERVERAGENT)服务的启动用户(发布&#x2F;订阅服务器均做此设置)<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">开始--程序--管理工具--服务<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--右键SQLSERVERAGENT<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--属性--登陆--选择&quot;此账户&quot;<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--输入或者选择第一步中创建的windows登录用户名（SynUser）<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--&quot;密码&quot;中输入该用户的密码<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">4.设置SQL Server身份验证模式,解决连接时的权限问题(发布&#x2F;订阅服务器均做此设置)<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">企业管理器<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--右键SQL实例--属性<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--安全性--身份验证<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--选择&quot;SQL Server 和 Windows&quot;<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--确定<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">5.在发布服务器和订阅服务器上互相注册<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">企业管理器<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--右键SQL Server组<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--新建SQL Server注册...<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--下一步--可用的服务器中,输入你要注册的远程服务器名 --添加<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--下一步--连接使用,选择第二个&quot;SQL Server身份验证&quot;<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--下一步--输入用户名和密码（SynUser）<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--下一步--选择SQL Server组,也可以创建一个新组<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--下一步--完成<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">6.对于只能用IP,不能用计算机名的,为其注册服务器别名（此步在实施中没用到）<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(在连接端配置,比如,在订阅服务器上配置的话,服务器名称中输入的是发布服务器的IP)<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">开始--程序--Microsoft SQL Server--客户端网络实用工具<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--别名--添加<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--网络库选择&quot;tcp&#x2F;ip&quot;--服务器别名输入SQL服务器名<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--连接参数--服务器名称中输入SQL服务器ip地址<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--如果你修改了SQL的端口,取消选择&quot;动态决定端口&quot;,并输入对应的端口号<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">二、 正式配置<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">1、配置发布服务器<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">打开企业管理器，在发布服务器（B、C、D）上执行以下步骤:<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(1) 从[工具]下拉菜单的[复制]子菜单中选择[配置发布、订阅服务器和分发]出现配置发布和分发向导<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(2) [下一步] 选择分发服务器 可以选择把发布服务器自己作为分发服务器或者其他sql的服务器（选择自己）<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(3) [下一步] 设置快照文件夹<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">采用默认\\servername\Pub<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(4) [下一步] 自定义配置<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">可以选择:是,让我设置分发数据库属性启用发布服务器或设置发布设置<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">否,使用下列默认设置（推荐）<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(5) [下一步] 设置分发数据库名称和位置 采用默认值<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(6) [下一步] 启用发布服务器 选择作为发布的服务器<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(7) [下一步] 选择需要发布的数据库和发布类型<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(8) [下一步] 选择注册订阅服务器<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(9) [下一步] 完成配置<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">2、创建出版物<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">发布服务器B、C、D上<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(1)从[工具]菜单的[复制]子菜单中选择[创建和管理发布]命令<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(2)选择要创建出版物的数据库，然后单击[创建发布]<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(3)在[创建发布向导]的提示对话框中单击[下一步]系统就会弹出一个对话框。对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助)<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(4)单击[下一步]系统要求指定可以订阅该发布的数据库服务器类型,<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">SQLSERVER允许在不同的数据库如 orACLE或ACCESS之间进行数据复制。<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">但是在这里我们选择运行&quot;SQL SERVER 2000&quot;的数据库服务器<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(5)单击[下一步]系统就弹出一个定义文章的对话框也就是选择要出版的表<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">注意: 如果前面选择了事务发布 则再这一步中只能选择带有主键的表<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(6)选择发布名称和描述<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(7)自定义发布属性 向导提供的选择:<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">是 我将自定义数据筛选,启用匿名订阅和或其他自定义属性<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">否 根据指定方式创建发布 （建议采用自定义的方式）<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(8)[下一步] 选择筛选发布的方式<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(9)[下一步] 可以选择是否允许匿名订阅<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">1)如果选择署名订阅,则需要在发布服务器上添加订阅服务器<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">方法: [工具]-&gt;[复制]-&gt;[配置发布、订阅服务器和分发的属性]-&gt;[订阅服务器] 中添加<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">否则在订阅服务器上请求订阅时会出现的提示:改发布不允许匿名订阅<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">如果仍然需要匿名订阅则用以下解决办法<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">[企业管理器]-&gt;[复制]-&gt;[发布内容]-&gt;[属性]-&gt;[订阅选项] 选择允许匿名请求订阅<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">2)如果选择匿名订阅,则配置订阅服务器时不会出现以上提示<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(10)[下一步] 设置快照 代理程序调度<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(11)[下一步] 完成配置<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">有数据<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">srv1.库名..author有字段:id,name,phone, <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">srv2.库名..author有字段:id,name,telphone,adress <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none"> <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">要求：<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">srv1.库名..author增加记录则srv1.库名..author记录增加<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">srv1.库名..author的phone字段更新，则srv1.库名..author对应字段telphone更新<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--*&#x2F; <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none"> <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--大致的处理步骤<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--1.在 srv1 上创建连接服务器,以便在 srv1 中操作 srv2,实现同步<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">exec sp_addlinkedserver &#39;srv2&#39;,&#39;&#39;,&#39;SQLOLEDB&#39;,&#39;srv2的sql实例名或ip&#39; <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">exec sp_addlinkedsrvlogin &#39;srv2&#39;,&#39;false&#39;,null,&#39;用户名&#39;,&#39;密码&#39; <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">go<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--2.在 srv1 和 srv2 这两台电脑中,启动 msdtc(分布式事务处理服务),并且设置为自动启动<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">。我的电脑--控制面板--管理工具--服务--右键 Distributed Transaction Coordinator--属性--启动--并将启动类型设置为自动启动<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">go <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none"> <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none"> <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--然后创建一个作业定时调用上面的同步处理存储过程就行了<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none"> <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">企业管理器<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--管理<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--SQL Server代理<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--右键作业<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--新建作业<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--&quot;常规&quot;项中输入作业名称<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--&quot;步骤&quot;项<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--新建<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--&quot;步骤名&quot;中输入步骤名<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--&quot;类型&quot;中选择&quot;Transact-SQL 脚本(TSQL)&quot; <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--&quot;数据库&quot;选择执行命令的数据库<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--&quot;命令&quot;中输入要执行的语句: exec p_process <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--确定<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--&quot;调度&quot;项<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--新建调度<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--&quot;名称&quot;中输入调度名称<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--&quot;调度类型&quot;中选择你的作业执行安排<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--如果选择&quot;反复出现&quot; <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--点&quot;更改&quot;来设置你的时间安排<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none"> <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none"> <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none"> <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">设置方法: <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择&quot;自动启动&quot;--确定. <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none"> <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none"> <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--3.实现同步处理的方法2,定时同步<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none"> <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--在srv1中创建如下的同步处理存储过程<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">create proc p_process <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">as <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--更新修改过的数据<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">update b set name&#x3D;i.name,telphone&#x3D;i.telphone <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">from srv2.库名.dbo.author b,author i <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">where b.id&#x3D;i.id and<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">(b.name &lt;&gt; i.name or b.telphone &lt;&gt; i.telphone) <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none"> <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--插入新增的数据<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">insert srv2.库名.dbo.author(id,name,telphone) <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">select id,name,telphone from author i <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">where not exists( <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">select * from srv2.库名.dbo.author where id&#x3D;i.id) <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none"> <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">--删除已经删除的数据(如果需要的话) <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">delete b <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">from srv2.库名.dbo.author b <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">where not exists( <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>

<pre class="line-numbers language-none"><code class="language-none">select * from author where id&#x3D;b.id)
go<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>

<p>正文结束</p>

      </div>
      <div class="post-tags-categories">
        
      </div>
      
        <div class="copyright">
  <ul class="post-copyright">
    <li class="post-copyright-author">
    <strong>author:  </strong>Boss</a>
    </li>
    <li class="post-copyright-link">
    <strong>link:  </strong>
    <a href="/2020/03/06/sql-yu-ju-da-quan-suo-you-de-sql-du-zai-zhe-li/" target="_blank" title="SQL语句大全，所有的SQL都在这里">http://www.gcblog.vip/2020/03/06/sql-yu-ju-da-quan-suo-you-de-sql-du-zai-zhe-li/</a>
    </li>
    <li class="post-copyright-license">
      <strong>Copyright notice:   </strong>
      All articles on this website, unless otherwise stated, adopt <a rel="license" href="https://creativecommons.org/licenses/by-nc-nd/4.0/" target="_blank" title="Attribution-NonCommercial-NoDerivatives 4.0 International (CC BY-NC-ND 4.0)">CC BY-NC-ND 4.0</a>
      reprint policy. If reproduced, please indicate source!
    </li>
  </ul>
<div>
      
    </article>
    <!-- 上一篇文章和下一篇文章 -->
    
      <!-- 文章详情页的上一页和下一页 -->
<div class="post-nav">



  
  <div class="post-nav-prev post-nav-item">
    <div class="post-nav-img" style="background-size: cover; 
      background-position: center center;">
      <img class="lazyload lazyload placeholder" src="/medias/8.jpg" class="lazyload placeholder" data-srcset="/medias/8.jpg" srcset="https://img2.baidu.com/it/u=2037979560,2772131037&fm=26&fmt=auto&gp=0.jpg" alt="">
    </div>
    <a href="/2020/03/06/bu-shi-yong-synchronized-he-lock-ru-he-shi-xian-yi-ge-xian-cheng-an-quan-de-dan-li/" class="post-nav-link">
      <div class="title">
        <i class="fas fa-angle-left"></i> Prev:
        <div class="title-text">不使用synchronized和lock，如何实现一个线程安全的单例？</div>
      </div>
      
      <!-- <div class="content">
        单例，大家肯定都不陌生，这是Java中很重要的一个设计模式。稍微了解一点单例的朋友也都知道实现单例是要考虑并发问题的，一
      </div> -->
    </a>
  </div>



  
  <div class="post-nav-next post-nav-item">
    <div class="post-nav-img" style="background-size: cover; 
      background-position: center center;">
      <img class="lazyload lazyload placeholder" src="/medias/15.jpg" class="lazyload placeholder" data-srcset="/medias/15.jpg" srcset="https://img2.baidu.com/it/u=2037979560,2772131037&fm=26&fmt=auto&gp=0.jpg" src="" alt="">
    </div>
    <a href="/2020/03/06/100-ge-wang-luo-ji-chu-zhi-shi-pu-ji-kan-wan-cheng-ban-ge-wang-luo-gao-shou/" class="post-nav-link">
      <div class="title">
        Next: <i class="fas fa-angle-right"></i>
        <div class="title-text">100 个网络基础知识普及，看完成半个网络高手！</div>
      </div>
      <!-- <div class="content">
        100 个网络基础知识普及，看完成半个网络高手！
1）什么是链接？链接是指两个设备之间的连接。它包括用于一个设备能够与另
      </div> -->
    </a>
  </div>

</div>

    
    

    <!-- 打赏 -->
    
      <div id="appDonate" class="post-donate">
  <div id="donate_board" class="donate_bar center" ref="donate">
    <a id="btn_donate" class="btn_donate" href="javascript:;" title="打赏" @click="showDialogDrawer()"></a>
  </div>
  <transition name="fade">
    <div 
      class="donate-box-mask"
      v-cloak 
      v-show="visible"
      @click="cancelDialogDrawer()"
    >
    </div>
  </transition>
  <transition name="bounce">
    <div class="donate-box" v-cloak v-show="visible">
      <div class="donate-box_close">
        <i class="fas fa-times" aria-hidden="true" @click="cancelDialogDrawer" pointer></i>
      </div>
      <div class="donate-box_title">
        <h4>
          你的赏识是我前进的动力
        </h4>
      </div>
      <div class="donate-box_tab">
        <div class="Alipay" pointer :class="{'active': tabActive === 'Alipay'}" @click="changeTabActive('Alipay')">
          支付宝
        </div>
        <div class="WeChatpay" pointer :class="{'active': tabActive === 'WeChatpay'}" @click="changeTabActive('WeChatpay')">
          微信
        </div>
      </div>
      <div class="donate-box_img">
        <div class="AlipayImg" v-show="tabActive === 'Alipay'">
          <img src="/medias/zfb.jpg" class="lazyload placeholder" data-srcset="/medias/zfb.jpg" srcset="https://img2.baidu.com/it/u=2037979560,2772131037&fm=26&fmt=auto&gp=0.jpg" alt="支付宝打赏" />
        </div> 
        <div class="WeChatpayImg" v-show="tabActive === 'WeChatpay'">
          <img src="/medias/wx.jpg" class="lazyload placeholder" data-srcset="/medias/wx.jpg" srcset="https://img2.baidu.com/it/u=2037979560,2772131037&fm=26&fmt=auto&gp=0.jpg" alt="微信打赏" />
        </div>
      </div>
    </div>
  </transition>
</div>

<script>
  var body = document.body || document.documentElement || window;
  var vm = new Vue({
    el: '#appDonate',
    data: {
      visible: false,
      tabActive: 'Alipay',
      top: 0,
    },
    computed: {
    },
    mounted() {
    },
    methods: {
      showDialogDrawer() {
        this.visible = true;
        // 防止页面滚动，只能让弹框滚动
        // function getScroll() {
        //   return {
        //     left: window.pageXOffset || document.documentElement.scrollLeft || document.body.scrollLeft || 0,
        //     top: window.pageYOffset || document.documentElement.scrollTop || document.body.scrollTop || 0
        //   };
        // }
        this.top = $(document).scrollTop() // or getScroll().top
        // console.log('aa', $('.main-content'));
        body.style.cssText = 'overflow: hidden;';
      },
      cancelDialogDrawer() {
        this.visible = false;
        body.removeAttribute('style');
        $(document).scrollTop(this.top)
      },
      changeTabActive(name) {
        this.tabActive = name;
      }
    },
    created() {}
  })
</script>
    

    <!-- 分享 -->
    
      <!-- https://github.com/overtrue/share.js -->
<!-- 文章详情页的分享 -->
<div class="social-share" data-sites="twitter,facebook,google,qq,qzone,wechat,weibo,douban,linkedin" data-wechat-qrcode-helper="<p>微信扫一扫即可分享！</p>"></div>

<script src="/js/shareJs/social-share.min.js"></script>
</script>

<style>
  .social-share {
    margin: 20px 0;
  }
</style>


    
    
    <!-- 评论 -->
    <!-- 评论 -->

  <div id="myComment">
    
      
<section id="comments" style="padding: 1em; margin: 15px auto;"
	class="animated bounceInUp">
	<div id="vcomment" class="comment"></div>
</section>
<style>
	#comments {
		background: rgba(255,255,255,0.9);
	}
	#veditor {
		background-image: url('https://img.zcool.cn/community/01a253594c71cfa8012193a329a77f.gif');
		background-size: contain;
		background-repeat: no-repeat;
		background-position: right;
		background-color: rgba(255, 255, 255, 0);
		resize: vertical;
	}
	#veditor:focus{
		background-position-y: 200px;
		transition: all 0.2s ease-in-out 0s;
	}
	#vcomment .vcards .vcard .vh .vhead .vtag.vvisitor {
		background-color: #42b983;
	}
	.v[data-class=v] .vbtn:active, .v[data-class=v] .vbtn:hover {
		color: #42b983;
		border-color: #42b983;
	}
	#vcomment .vcards .vcard .vhead .vsys i {
		display: none;
	}
	/* 底部valine链接 */
	#vcomment .vpower {
		display: none;
	}
	
	/* 底下注释是修改 名称和邮箱和网址输入框的样式 */
	/* #vcomment .vheader {
		display: flex;
		justify-content: space-around;
	}
	
	#vcomment .vheader .vnick {
		width: 31%;
		border: 2px solid #dedede;
		padding-left: 10px;
		padding-right: 10px;
		border-radius: 5px
	}

	#vcomment .vheader .vmail {
		width: 31%;
		border: 2px solid #dedede;
		padding-left: 10px;
		padding-right: 10px;
		border-radius: 5px
	}

	#vcomment .vheader .vlink {
		width: 31%;
		border: 2px solid #dedede;
		padding-left: 10px;
		padding-right: 10px;
		border-radius: 5px
	} */

	img.vimg {
		transition: all 1s;
		/* 头像旋转时间为 1s */
	}

	img.vimg:hover {
		transform: rotate(360deg);
		-webkit-transform: rotate(360deg);
		-moz-transform: rotate(360deg);
		-o-transform: rotate(360deg);
		-ms-transform: rotate(360deg);
	}

	#vcomment .vcards .vcard {
		padding: 15px 20px 0 20px;
		border-radius: 10px;
		margin-bottom: 15px;
		box-shadow: 0 0 4px 1px rgba(0, 0, 0, .12);
		transition: all .3s
	}

	#vcomment .vcards .vcard:hover {
		box-shadow: 0 0 8px 3px rgba(0, 0, 0, .12)
	}

	#vcomment .vcards .vcard .vh .vcard {
		border: none;
		box-shadow: none;
	}
</style>
    
  </div>

<!-- comment script in themes\hexo-theme-bamboo\layout\_partial\scripts\index.ejs -->


  </div>

  <!-- 目录 -->
  <!-- 文章详情页右侧目录 -->

  <div class="toc-aside">
    <div class="toc-main">
      <div class="toc-aside-title">
        <i class="fas fa-list-ul" aria-hidden="true"></i><span>catalog</span>
        
          <div class="toc-open-close">catalog</div>
        
      </div>
      <div class="toc-content">
        <div class="toc"></div>
      </div>
    </div>
  </div>

  <!-- 手机端目录按钮 -->
  <div id="toc-mobile-btn">
    <i class="fas fa-list-ul" aria-hidden="true"></i>
  </div>


<script>
  function closeToc(init) {
    $(".toc-aside").css({'width': 0, 'padding': 0, 'transition': init ?  'noe' : 'width 0.3s' });
    $(".toc-content").css({'width': 0});
    $(".toc-aside-title span, .toc-aside-title i").css({'display': 'none'});
    $(".main-content").css({'width': '75%', 'margin': '10px auto'});
  };
  function openToc() {
    $(".main-content").css({'width': '65%', 'margin-right': '10px', 'margin-left': 'calc(35% - 350px)'});
    $(".toc-aside").css({'width': '300px', 'padding': '0 10px', 'transition': 'width 0.3s'});
    $(".toc-content").css({'width': '300px'});
    $(".toc-aside-title span, .toc-aside-title i").css({'display': 'inline-block'});
  }
  function openBtnClickFn () {
    let openOrCloseBtn = $('.toc-aside .toc-aside-title .toc-open-close');
    let open = eval('' || 'true');
    openOrCloseBtn.click(function() {
      if (open) {
        closeToc();
        open = false;
      } else {
        openToc();
        open = true;
      }
    });
  };
  openBtnClickFn();
  initCloseTocWidth(true);

  function initCloseTocWidth(init) {
    if (window.innerWidth >= 992) {
      let isClose = false;
      isClose && closeToc(init)
    }
  }

  document.addEventListener('pjax:complete', function () {
    $(".toc-aside").css({'transition': 'no'});
  })
  document.addEventListener('pjax:complete', function () {
    openBtnClickFn();
  })
  
</script>

  <!-- 图片放大 Wrap images with fancybox support -->
  <script src="/js/wrapImage.js"></script>
</div>

<!-- 文章详情页背景图 -->
<div id="appBgSwiper" style="position: fixed;left: 0;top: 0;width: 100%;height: 100%;z-index: -2;"
	:style="{'background-color': bgColor ? bgColor : 'transparent'}">
	<transition-group tag="ul" :name="names">
		<li v-for='(image,index) in img' :key='index' v-show="index === mark" class="bg-swiper-box">
			<img :src="image" class="bg-swiper-img no-lazy">
		</li>
	</transition-group>
</div>
<script>
	var vm = new Vue({
		el: '#appBgSwiper',
		data: {
			names: '' || 'fade' || 'fade', // translate-fade fade
			mark: 0,
			img: [],
			bgColor: '',
			time: null
		},
		methods: {   //添加方法
			change(i, m) {
				if (i > m) {
					// this.names = 'fade';
				} else if (i < m) {
					// this.names = 'fade';
				} else {
					return;
				}
				this.mark = i;
			},
			prev() {
				// this.names = 'fade';
				this.mark--;
				if (this.mark === -1) {
					this.mark = 3;
					return
				}
			},
			next() {
				// this.names = 'fade';
				this.mark++;
				if (this.mark === this.img.length) {
					this.mark = 0;
					return
				}
			},
			autoPlay() {
				// this.names = 'fade';
				this.mark++;
				if (this.mark === this.img.length) {
					this.mark = 0;
					return
				}
			},
			play() {
				let bgImgDelay = '' || '180000'
				let delay = parseInt(bgImgDelay) || 180000;
				this.time = setInterval(this.autoPlay, delay);
			},
			enter() {
				clearInterval(this.time);
			},
			leave() {
				this.play();
			}
		},
		created() {
			this.play()
		},
		beforeDestroy() {
			clearInterval(this.time);
		},
		mounted() {
			let prop = '' || '';
			let isImg = prop.includes('.bmp') || prop.includes('.jpg') || prop.includes('.png') || prop.includes('.tif') || prop.includes('.gif') || prop.includes('.pcx') || prop.includes('.tga') || prop.includes('.exif') || prop.includes('.fpx') || prop.includes('.psd') || prop.includes('.cdr') || prop.includes('.pcd') || prop.includes('.dxf') || prop.includes('.ufo') || prop.includes('.eps') || prop.includes('.ai') || prop.includes('.raw') || prop.includes('.WMF') || prop.includes('.webp') || prop.includes('.jpeg') || prop.includes('http://') || prop.includes('https://')
			if (isImg) {
				let img = prop.split(',');
				let configRoot = '/'
				let arrImg = [];
				img.forEach(el => {
					var Expression = /http(s)?:\/\/([\w-]+\.)+[\w-]+(\/[\w- .\/?%&=]*)?/;
					var objExp = new RegExp(Expression);

					if (objExp.test(el)) {
						// http or https
						arrImg.push(el);
					} else {
						// 非http or https开头
						// 本地文件
						let firstStr = el.charAt(0);
						if (firstStr == '/') {
							el = el.substr(1); // 删除第一个字符 '/',因为 configRoot最后一个字符为 /
						}
						el = configRoot + el;
						arrImg.push(el);
					}
				})
				this.img = arrImg;
			} else {
				this.bgColor = prop;
			}
		}
	})
</script>

<style>
	.bg-swiper-box {
		position: absolute;
		display: block;
		width: 100%;
		height: 100%;
	}

	.bg-swiper-img {
		object-fit: cover;
		width: 100%;
		height: 100%;
	}
</style>




  <script>
  function loadMermaid() {
    if (document.getElementsByClassName('mermaid').length) {
      if (window.mermaidJsLoad) mermaid.init()
      else {
        loadScript('https://cdn.jsdelivr.net/npm/mermaid/dist/mermaid.min.js').then(() => {
          window.mermaidJsLoad = true
          mermaid.initialize({
            theme: 'default',
          })
          if ('true') {
            mermaid.init();
          }
        })
      }
    }
  };
  document.addEventListener("DOMContentLoaded", function () {
    loadMermaid();
  })

  document.addEventListener('pjax:complete', function () {
    loadMermaid();
  })
  
</script>


      </main>
    </div>

    <!-- 页脚 -->
    
  
  <div class="footer bg-color">
    <div class="footer-main">
      
        
          <div class="link">
            
              
                <a target="_blank" rel="noopener" href="http://baidu.com" class="social">
                  
                    <img src="https://seikim.com/i/2021/06/15/h4fynr.svg" class="lazyload placeholder" data-srcset="https://seikim.com/i/2021/06/15/h4fynr.svg" srcset="https://img2.baidu.com/it/u=2037979560,2772131037&fm=26&fmt=auto&gp=0.jpg"/>
                  
                </a>
              
            
              
                <a target="_blank" rel="noopener" href="http://baidu.com" class="social">
                  
                    <img src="https://seikim.com/i/2021/06/15/h4fynr.svg" class="lazyload placeholder" data-srcset="https://seikim.com/i/2021/06/15/h4fynr.svg" srcset="https://img2.baidu.com/it/u=2037979560,2772131037&fm=26&fmt=auto&gp=0.jpg" style="border-radius:120px"/>
                  
                </a>
              
            
          </div>
        
      
        
          <div class="footer-copyright">
            <p>Copyright © 2019 - 2020 <a target="_blank" rel="noopener" href="https://github.com/yuang01">yuang01</a> | Powered by <a target="_blank" rel="noopener" href="https://hexo.io/zh-cn/docs/">Hexo</a> | Theme <a target="_blank" rel="noopener" href="https://github.com/yuang01/theme">Bamboo</a> </p>

          </div>
        
      
        
          
            <!-- 不蒜子统计 -->
            <!-- 不蒜子统计 -->
<span id="busuanzi_container_site_pv">
      <i class="fas fa-eye" aria-hidden="true"></i>Total visits：<span id="busuanzi_value_site_pv"></span> times
</span>
<span class="post-meta-divider">|</span>
<span id="busuanzi_container_site_uv" style='display:none'>
      <i class="fas fa-users" aria-hidden="true"></i>Number of visitors：<span id="busuanzi_value_site_uv"></span> people
</span>

          
        
      
        
          <div class="footer-custom">
            
          </div>
        
      
    </div>
  </div>



    <!-- 渲染暗黑按钮 -->
    
      <div class="dark">
  <div class="dark-content">
    <i class="fas fa-moon" aria-hidden="true"></i>
    <!-- <span>关灯</span> -->
  </div>
  
</div>

<script>
  $(function() {
    let isDark = JSON.parse(localStorage.getItem('dark'))  || JSON.parse('false');
    if (isDark) {
      $(".dark-content").replaceWith(
          `
          <div class='dark-content'>
            <i class="fas fa-lightbulb" aria-hidden="true"></i>
          </div>
          `
        );
    }
    $('.dark').click(function() {
      if ($(document.body).is('.darkModel')) {
        $(document.body).removeClass('darkModel');
        localStorage.setItem('dark', false);
        $(".dark-content").replaceWith(
          `
          <div class='dark-content'>
            <i class="fas fa-moon" aria-hidden="true"></i>
          </div>
          `
        );
      } else {
        $(document.body).addClass('darkModel');
        localStorage.setItem('dark', true);
        $(".dark-content").replaceWith(
          `
          <div class='dark-content'>
            <i class="fas fa-lightbulb" aria-hidden="true"></i>
          </div>
          `
        );
      }
    })
  })
</script>
    
    <!-- 渲染回到顶部按钮 -->
    
      <div class="goTop top-btn-color" pointer>
  <i class="fas fa-arrow-up" aria-hidden="true"></i>
</div>
<script src="/js/goTop.js"></script>

    
    <!-- 渲染左下角音乐播放器 -->
    
      <link rel="stylesheet" href="/js/aplayer/APlayer@1.10.1.min.css">
<style>
.aplayer .aplayer-lrc p {
  
  font-size: 12px;
  font-weight: 700;
  line-height: 16px !important;
}

.aplayer .aplayer-lrc p.aplayer-lrc-current {
  
  font-size: 15px;
  color: #42b983;
}


.aplayer.aplayer-fixed.aplayer-narrow .aplayer-body {
  left: -66px !important;
}

.aplayer.aplayer-fixed.aplayer-narrow .aplayer-body:hover {
  left: 0px !important;
}


</style>
<meting-js  
  class=""
  server="tencent"
  type="playlist"
  id="8062553743"
  fixed='true'
  autoplay='true'
  theme='#42b983'
  loop='none'
  order='list'
  preload='auto'
  volume='0.4'
  list-folded='false'
>
</meting-js>

<!-- <style>
  #aplayer {
    position: fixed;
    left: 0;
    bottom: 300px;
  }
</style> -->
<script src="https://cdn.jsdelivr.net/npm/aplayer@1.10.1/dist/APlayer.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/meting@2/dist/Meting.min.js"></script>
    

    <!-- 图片放大 -->
    
      <script src="/js/fancybox/jquery.fancybox.min.js"></script>
    

    <!-- 百度解析 -->
    <!-- Baidu Analytics -->

    <!-- Baidu Push -->

<script>
    (function () {
        var bp = document.createElement('script');
        var curProtocol = window.location.protocol.split(':')[0];
        if (curProtocol === 'https') {
            bp.src = 'https://zz.bdstatic.com/linksubmit/push.js';
        } else {
            bp.src = 'http://push.zhanzhang.baidu.com/push.js';
        }
        var s = document.getElementsByTagName("script")[0];
        s.parentNode.insertBefore(bp, s);
    })();
</script>

    
    <!-- 背景彩带 -->
    
      <script type="text/javascript" size="100" alpha='0.4' zIndex="-1" src="/js/ribbon.min.js"></script>
    

    <script src="/js/utils/index.js"></script>
    <script src="/js/app.js"></script>
    
    <!-- 文章目录所需js -->
<link href="/js/tocbot/tocbot.css" rel="stylesheet">
<script src="/js/tocbot/tocbot.min.js"></script>

<script>
  var headerEl = 'h2, h3, h4',  //headers 
    content = '.post-detail',//文章容器
    idArr = {};  //标题数组以确定是否增加索引id
  //add #id
  var option = {
    // Where to render the table of contents.
    tocSelector: '.toc',
    // Where to grab the headings to build the table of contents.
    contentSelector: content,
    // Which headings to grab inside of the contentSelector element.
    headingSelector: headerEl,
    scrollSmooth: true,
    scrollSmoothOffset: -80,
    headingsOffset: -($(window).height() * 0.4 - 45),
    positionFixedSelector: '.toc-main',
    positionFixedClass: 'is-position-fixed',
    fixedSidebarOffset: 'auto',
    activeLinkClass: 'is-active-link',
    // onClick: function (e) {},
  }
  if ($('.toc').length > 0) {

    $(content).children(headerEl).each(function () {
      //去除空格以及多余标点
      var headerId = $(this).text().replace(/[\s|\~|`|\!|\@|\#|\$|\%|\^|\&|\*|\(|\)|\_|\+|\=|\||\|\[|\]|\{|\}|\;|\:|\"|\'|\,|\<|\.|\>|\/|\?|\：|\，|\。]/g, '');

      headerId = headerId.toLowerCase();
      if (idArr[headerId]) {
        //id已经存在
        $(this).attr('id', headerId + '-' + idArr[headerId]);
        idArr[headerId]++;
      }
      else {
        //id未存在
        idArr[headerId] = 1;
        $(this).attr('id', headerId);
      }
    });

    document.addEventListener("DOMContentLoaded", function () {
      tocbot.init(option);
      mobileTocClick();
    });

  }

  window.tocScrollFn = function () {
    return bamboo.throttle(function () {
      findHeadPosition();
    }, 100)()
  }
  window.addEventListener('scroll', tocScrollFn);

  const findHeadPosition = function (top) {
    if ($('.toc-list').length <= 0) {
      return false;
    }
    setTimeout(() => {  // or DOMContentLoaded 
      autoScrollToc();
    }, 0);
  }

  const autoScrollToc = function () {
    const $activeItem = document.querySelector('.is-active-link');
    const $cardToc = document.querySelector('.toc-content');
    const activePosition = $activeItem.getBoundingClientRect().top
    const sidebarScrollTop = $cardToc.scrollTop
    if (activePosition > (document.documentElement.clientHeight - 100)) {
      $cardToc.scrollTop = sidebarScrollTop + 150
    }
    if (activePosition < 100) {
      $cardToc.scrollTop = sidebarScrollTop - 150
    }
  }

  document.addEventListener('pjax:send', function () {
    if ($('.toc').length) {
      tocbot.destroy();
    }
  });

  document.addEventListener('pjax:complete', function () {
    if ($('.toc').length) {
      tocbot.init(option);
      mobileTocClick();
    }
  });
  
  // 手机端toc按钮点击出现目录
  const mobileTocClick = function () {
    const $cardTocLayout = document.getElementsByClassName('toc-aside')[0];
    const $cardToc = $cardTocLayout.getElementsByClassName('toc-content')[0];
    let right = '45px';
    if (window.innerWidth >= 551 && window.innerWidth <= 992) {
      right = '100px'
    }
    const mobileToc = {
      open: () => {
        $cardTocLayout.style.cssText = 'animation: toc-open .3s; opacity: 1; right: ' + right
      },

      close: () => {
        $cardTocLayout.style.animation = 'toc-close .2s'
        setTimeout(() => {
          $cardTocLayout.style.cssText = "opacity:''; animation: ''; right: ''"
        }, 100)
      }
    }
    document.getElementById('toc-mobile-btn').addEventListener('click', () => {
      if (window.getComputedStyle($cardTocLayout).getPropertyValue('opacity') === '0') mobileToc.open()
      else mobileToc.close()
    })

    $cardToc.addEventListener('click', (e) => {
      if (window.innerWidth < 992) { // 小于992px的时候
        mobileToc.close()
      }
    })
  }
</script>

<style>
  .is-position-fixed {
    position: sticky !important;
    top: 74px;
  }

  .toc-main ul {
    counter-reset: show-list;
  }

  .toc-main ul li::before {
    content: counter(item)".";
    display: block;
    position: absolute;
    left: 12px;
    top: 0;
  }
</style> 

<!-- 设置导航背景 -->
<script>
  let setHeaderClass = () => {
    const headerMenuTransparent = true;
    if (!headerMenuTransparent) { return; }
    const nav = $('#navHeader');
    const navTop = nav.outerHeight();
    const winTop = $(window).scrollTop();
    if(winTop > navTop) {
      nav.addClass('header-bg-color');
    }
    else {
      nav.removeClass('header-bg-color');
    }
  };

  let scrollCollect = () => {
    return bamboo.throttle(function (e) {
      setHeaderClass();
    }, 200)()
  }

  let initHeaderBg = () => {
    setHeaderClass();
  }

  setHeaderClass();
  window.addEventListener('scroll', scrollCollect);

  document.addEventListener('pjax:send', function () {
    window.removeEventListener('scroll', scrollCollect)
  })
  document.addEventListener('pjax:complete', function () {
    window.addEventListener('scroll', scrollCollect);
    setHeaderClass();
  })
</script> 

<!-- 渲染issues标签里的内容 -->
<script>
  function loadIssuesJS() {
    if ($(".post-detail").find(".issues-api").length == 0) {
      return;
    } 
    loadScript('/js/issues/index.js');
  };
  $(function () {
    loadIssuesJS();
  });
  document.addEventListener('pjax:complete', function () {
    if (typeof IssuesAPI == "undefined") {
      loadIssuesJS();
    }
  })
</script>

<!-- 输入框打字特效 -->
<!-- 输入框打字特效 -->

  <script src="/js/activate-power-mode.js"></script>
  <script>
    POWERMODE.colorful = true;  // 打开随机颜色特效
    POWERMODE.shake = false;    // 关闭输入框抖动
    document.body.addEventListener('input', POWERMODE);//监听打字事件
  </script>


<!-- markdown代码一键复制功能 -->

  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/v-plugs-ayu/lib/ayu.css">
  <script src="https://cdn.jsdelivr.net/npm/v-plugs-ayu/lib/ayu.umd.min.js"></script>
  <script src="/js/clipboard/clipboard.min.js"></script>
  <div id="appCopy">
  </div>
  <script data-pjax>
    var vm = new Vue({
      el: '#appCopy',
      data: {
      },
      computed: {
      },
      mounted() {
        const that = this;
        var copy = 'copy';
        /* code */
        var initCopyCode = function () {
          var copyHtml = '';
          copyHtml += '<button class="btn-copy" data-clipboard-snippet="" style="position:absolute;top:0;right:0;z-index:1;">';
          copyHtml += '<i class="fas fa-copy"></i><span>' + copy + '</span>';
          copyHtml += '</button>';
          $(".post-detail pre").not('.gutter pre').wrap("<div class='codeBox' style='position:relative;width:100%;'></div>")
          $(".post-detail pre").not('.gutter pre').before(copyHtml);
          new ClipboardJS('.btn-copy', {
            target: function (trigger) {
              return trigger.nextElementSibling;
            }
          });
        }
        initCopyCode();
        $('.btn-copy').unbind('click').bind('click', function () {
          doSomething();
        })
        $(document).unbind('keypress').bind('keypress', function (e) {
          if (e.ctrlKey && e.keyCode == 67) {
            doSomething();
          }
        })

        function doSomething() {
          that.$notify({
            title: "成功",
            content: "代码已复制，请遵守相关授权协议。",
            type: 'success'
          })
        }
      },
      methods: {
      },
      created() { }
    })
  </script>
  

<!-- 图片懒加载 -->
<script defer src="https://cdn.jsdelivr.net/npm/vanilla-lazyload@17.1.0/dist/lazyload.min.js"></script>
<script>
  // https://www.npmjs.com/package/vanilla-lazyload
  // Set the options globally
  // to make LazyLoad self-initialize
  window.lazyLoadOptions = {
    elements_selector: ".lazyload",
    threshold: 0
  };
  // Listen to the initialization event
  // and get the instance of LazyLoad
  window.addEventListener(
    "LazyLoad::Initialized",
    function (event) {
      window.lazyLoadInstance = event.detail.instance;
    },
    false
  );
  document.addEventListener('DOMContentLoaded', function () {
    lazyLoadInstance.update();
  });
  document.addEventListener('pjax:complete', function () {
    lazyLoadInstance.update();
  });
</script>


<!-- 卡片滚动动画 -->

    <script type="text/javascript">
  loadScript("https://cdn.jsdelivr.net/npm/scrollreveal@4.0.6/dist/scrollreveal.min.js")
  function pjax_scrollrebeal() {
    ScrollReveal().reveal('.reveal', {
      distance: '120px',
      duration: '400',
      interval: '30',
      scale: '1',
      origin: 'bottom', // 动画开始的方向
      easing: 'ease'
    });
  }
  $(function () {
    var checkScrollReveal = setInterval(function () {
      if ($("#safearea").css("display") != "block") return
      if (typeof ScrollReveal == "undefined") return
      clearInterval(checkScrollReveal)
      pjax_scrollrebeal();
    }, 100)
  });
  document.addEventListener('pjax:complete', function () {
    pjax_scrollrebeal();
  })
</script>
   

<!-- 评论所需js -->

  
    <!-- 弹幕所需的css和js -->
    
  <!-- 弹幕插件 -->
  <link href="/js/danmu/barrager.css" rel="stylesheet">
  <script src="/js/danmu/jquery.barrager.js"></script>

<!-- 具体js，请前往valine/script.ejs查看 -->

    <script>
  var requiredFields = 'nick,mail';
  requiredFields = requiredFields.split(',');
  comment_el = '.comment';
  let looperValine = null;
  load_valine = function () {
    if ($(comment_el).length) {
      var valine = new Valine({
        el: '#vcomment',
        path: window.location.pathname,
        notify: false,
        verify: false,
        app_id: "oG5OWzf0iJlMbDC7nVxtI3Al-gzGzoHsz",
        app_key: "wEMz5fleopxe0QQezOHR36Xd",
        placeholder: "客官，说点什么吧",
        avatar: "",
        master: "8d6fa8c3f6fba9a3282f42007459faf4",   //博主邮箱md5
        tagMeta: ["博主","小伙伴","访客"],     //标识字段名
        friends: "8d6fa8c3f6fba9a3282f42007459faf4",
        metaPlaceholder: { "nick": "昵称/QQ号", "mail": "邮箱" },
        requiredFields: requiredFields,
        enableQQ: true,
      });
      function debounce(fn) {
        var timerID = null
        return function () {
          var arg = arguments[0]   //获取事件
          if (timerID) {
            clearTimeout(timerID)
          }
          timerID = setTimeout(function () {
            fn(arg)              //事件传入函数
          }, 200)
        }
      }
      //查询评论 valine.Q('*').limit(7) -- 查询所有，限制7条, 下面的的代码是查询当前页面
      var themeDanmu = eval('true');
      var themeLoop = eval('true');
      var themeLooperTime = '5000' || 5000;
      var speed = '40' || 20;
      var isBarrager = true;
      if (themeDanmu == true) {
        do_barrager();
        if ($('.danmuBox').length <= 0) {
          $('.navbar').append('<div class="danmuBox"><div class="danmuBtn open"><span class="danmuCircle"></span><span class="danmuText">弹幕</span></div></div>');
        }
        $('.danmuBtn').on('click', debounce(
          function () {
            if ($('.danmuBtn').hasClass('open')) {
              $('.danmuBtn').removeClass('open')
              clearInterval(looperValine);
              $.fn.barrager.removeAll();
            } else {
              $('.danmuBtn').addClass("open");
              do_barrager();
            }
          }
        ))
      }
      function do_barrager() {
        isBarrager && valine.Q(window.location.pathname).find().then(function (comments) {
          // var num = 0; // 可以记录条数，循环调用的时候只取最新的评论放入弹幕中
          var run_once = true;
          var looper_time = themeLooperTime;
          var total = comments.length;
          // var looper = null;
          var index = 0;
          if (total > 0) {
            barrager();
          } else {
            // 当评论数为0的时候，自动关闭弹幕
            // $('.danmuBtn').removeClass('open');
          }
          function barrager() {
            if (run_once) {
              //如果是首次执行,则设置一个定时器,并且把首次执行置为false
              looperValine = setInterval(barrager, looper_time);
              run_once = false;
            }
            var content = comments[index]._serverData.comment;
            var email = comments[index]._serverData.mail;
            var link = comments[index]._serverData.link;
            var newcontent = content.substring(0, 50).replace(/<[^>]+>/g, "");
            //发布一个弹幕
            const item = {
              img: `https://q1.qlogo.cn/g?b=qq&nk=${email}&s=640`, //图片 
              info: newcontent, //文字 
              href: link, //链接 
              close: true, //显示关闭按钮 
              speed: speed, //延迟,单位秒,默认6 
              color: '#fff', //颜色,默认白色 
              old_ie_color: '#000000', //ie低版兼容色,不能与网页背景相同,默认黑色
            }
            $('body').barrager(item);
            //索引自增
            index++;
            //所有弹幕发布完毕，清除计时器。
            if (index == total) {
              clearInterval(looperValine);
              if (themeLoop === true) {
                setTimeout(function () {
                  do_barrager();
                }, 5000);
              } else {
                $('.danmuBtn').removeClass('open');
              }
              return false;
            }

          }
        })
      }
    }
  };
  $(document).ready(load_valine);
  document.addEventListener('pjax:send', function (e) {
    
      $('.danmuBox').length > 0 && $('.danmuBox').remove()
      looperValine && clearInterval(looperValine);
      $.fn.barrager.removeAll();
    
  })
  document.addEventListener('pjax:complete', function () {
    load_valine();
  });
</script>

  


<!-- 鼠标点击特效 -->
<!-- 爱心点击 -->

  
    <canvas class="fireworks" style="position: fixed;left: 0;top: 0;z-index: 999; pointer-events: none;" ></canvas>
    <script src="//cdn.bootcss.com/animejs/2.2.0/anime.min.js"></script>
    <script src="/js/cursor/explosion.min.js"></script>
  




  <script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js" data-pjax></script>


    <!-- pjax -->
    

<!-- pjax -->


  <script src="/js/pjax@0.2.8/index.js"></script>
  
    <!-- 样式位于：source/css/_third-party/pjaxanimate.styl -->

<div class="pjax-animate">
  
    <div class="loading-circle"><div id="loader-circle"></div></div>
    <script>
      window.ShowLoading = function() {
        $(".loading-circle").css("display", "block");
      };
      window.HideLoading = function() {
        $(".loading-circle").css("display", "none");
      }
    </script>
  
	<script>
    document.addEventListener('pjax:complete', function () {
      window.HideLoading();
    })
    document.addEventListener('pjax:send', function () {
      window.ShowLoading();
    })
    document.addEventListener('pjax:error', function () {
      window.HideLoading();
    })
	</script>
</div>

  

  <script>
    var pjax = new Pjax({
      elements: 'a[href]:not([href^="#"]):not([href="javascript:void(0)"]):not([no-pjax])',   // 拦截正常带链接的 a 标签
      selectors: ["#pjax-container","title"],                                   // 根据实际需要确认重载区域
      cacheBust: false,   // url 地址追加时间戳，用以避免浏览器缓存
      timeout: 5000
    });

    document.addEventListener('pjax:send', function (e) {

      try {
        var currentUrl = window.location.pathname;
        var targetUrl = e.triggerElement.href;
        var banUrl = [""];
        if (banUrl[0] != "") {
          banUrl.forEach(item => {
            if(currentUrl.indexOf(item) != -1 || targetUrl.indexOf(item) != -1) {
              window.location.href = targetUrl;
            }
          });
        }
      } catch (error) {}

      $(window).unbind('resize');
      $(window).unbind('scroll');
      $(document).unbind('scroll');
      $(document).unbind('click');
      $('body').unbind('click');

    })
    
    document.addEventListener('pjax:complete', function () {
      $('script[data-pjax], .pjax-reload script').each(function () {
        $(this).parent().append($(this).remove());
      });
    });

    document.addEventListener('pjax:error', function (e) {
      window.location.href = e.triggerElement.href;
    })
    
    // 刷新不从顶部开始
    document.addEventListener("DOMContentLoaded", function () {
      history.scrollRestoration = 'auto';
    })
  </script>



  </body>
</html>